package com.demo.word;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.tomcat.util.http.fileupload.FileUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

/**
 * @author gy
 * @date 2024/1/2
 */
public class ReadExcelData {

    public static Workbook getWorkbook1(String filePath) throws Exception {
        File file = new File(filePath);
        String fileName = file.getName();
        Workbook workbook = null;
        try (FileInputStream inputStream = new FileInputStream(file)) {
            String fileType = fileName.substring(fileName.lastIndexOf("."));
            if (".xls".equals(fileType)) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (".xlsx".equals(fileType)) {
                workbook = new XSSFWorkbook(inputStream);
            } else {
                System.out.println("非Excel文件");;
            }
        }
        return workbook;
    }

    public static Workbook getWorkbook2(String filePath) throws IOException {
        InputStream inputStream = new FileInputStream(filePath);
        return WorkbookFactory.create(inputStream);
    }

//    public static Workbook getWorkbook3(String filePath) throws IOException {
//        FileInputStream inputStream = FileUtils.openInputStream(new File(filePath));
//        return WorkbookFactory.create(inputStream);
//    }

    public static void getCellForI(Workbook workbook) throws Exception {
        Sheet sheet;
        Row row;
        Cell cell;
        // 2sheet页时则NumberOfSheets=2，下标索引取值0-1
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            sheet = workbook.getSheetAt(i);
            // 6行数据时,FirstRowNum=0,LastRowNum=5
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                // 4列时,FirstCellNum=0,LastCellNum=4
                for (int k = row.getFirstCellNum(); k < row.getLastCellNum(); k++) {
                    cell = row.getCell(k);
                    // String stringCellValue = convertCellValueToString(cell);
                    System.out.print(workbook.getNumberOfSheets() + "-" + sheet.getFirstRowNum() + "-" + sheet.getLastRowNum() + "-" + row.getFirstCellNum() + "-" + row.getLastCellNum() + ":" + cell + "  ");
                }
                System.out.println();
            }
            System.out.println();
        }
        workbook.close();
    }

    public static void getCell2ForEach(Workbook workbook) throws Exception {
        Sheet sheet;
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            sheet = workbook.getSheetAt(i);
            for (Row row : sheet) {
                for (Cell cell : row) {
                    // String stringCellValue = convertCellValueToString(cell);
                    System.out.print(cell + "  ");
                }
                System.out.println();
            }
            System.out.println();
        }
        workbook.close();
    }


}
